--------------------------------------------------------------------------------
-- 按照从：建库->建表->插入数据->修改数据->查询数据->连接查询 
--------------------------------------------------------------------------------


--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库，名称为Student
-- 检测 Student 库是否存在，如果存在则删除
use master
go
if exists(select * from sysdatabases where name='Student')
drop database Student
go
-- 创建学生库 Student
create database Student
go
-- 使用（切换）到 Student 库
use Student
--------------------
-- 建表部分
--------------------
-- 创建班级表 Class，存储班级信息，其中字段包含：班级id、班级名称
create table Class
(
	ClassId int primary key identity(1,1) not null,
	ClassName nvarchar(10),
)
go
-- 创建学生表 Student，存储学生信息，其中字段包含：学生id、姓名、性别、生日、家庭住址
create table Student
(
	StudentId int primary key identity(1,1) not null,
	StudentName nvarchar(20),
	StudentSex tinyint,
	StudentBirthday date,
	StudentAddress nvarchar(50),
)
go
-- 创建课程表 Course，存储课程信息，其中字段包含：课程id、课程名称、课程学分
create table Course
(
	CourseId int primary key identity(1,1) not null,
	CourseName nvarchar(20),
	CourseScore int,
)
go
-- 创建班级课程表 ClassCourse，存储班级课程信息，其中字段包含：自增id、班级id、课程id
create table ClassCourse
(
	ClassCourseId int primary key identity(1,1) not null,
	ClassId int,
	CourseId int,
)
go
-- 创建分数表 Score，存储学生每个课程分数信息，其中字段包含：分数id、学生id、课程id、分数
create table Score
(
	ScoreId int primary key identity(1,1) not null,
	StudentId int,
	CourseId int,
	Score int,
)
go
-- 创建老师表 Teacher，存储老师信息，其中包括：教师id、姓名、性别、生日
create table Teacher
(
	TeacherId int primary key identity(1,1) not null,
	TeacherName nvarchar(20),
	TeacherSex tinyint,
	TeacherBirthday date,
)
go
-- 老师表 Teacher 现在还用不上，删除这个表
drop table Teacher
go
-- 学生表建好了，细想一下少了一个所属班级的字段
-- 给学生表 Student 增加一个所属班级id字段 
alter table Student add ClassId int
go
----------------------------------------
-- 创建约束部分，使用alter进行修改
----------------------------------------
-- 班级表 Class.ClassId 字段需要设置为主键（主键约束）
alter table Class add constraint PK_ClassId primary key(ClassId)
-- 学生表 Student.StudentId 字段需要设置为主键（主键约束）
alter table Student add constraint PK_StudentId primary key(StudentId)
-- 课程表 Course.CourseId 字段需要设置为主键（主键约束）
alter table Course add constraint PK_CourseId primary key(CourseId)
-- 班级课程表 ClassCourse.ClassCourseId 字段需要设置为主键（主键约束）
alter table ClassCourse add constraint PK_ClassCourseId primary key(ClassCourseId) 
-- 分数表 Score.ScoreId 字段需要设置为主键（主键约束）
alter table Score add constraint PK_ScoreId primary key(ScoreId)
-- 学生表 Student.ClassId 增加默认值为0（默认值约束）
alter table Student add constraint DF_ClassId default(0) for ClassId
-- 班级表 Class.ClassName 需要唯一（唯一约束）
alter table Class add constraint UQ_ClassName unique(ClassName)
-- 课程表 Course.CourseName 需要唯一（唯一约束）
alter table Course add constraint UQ_CourseName unique(CourseName)
-- 学生表 Student.StudentName 不允许为空（非空约束）
alter table Student alter column StudentName nvarchar(20) not null
-- 学生表 Student.StudentSex 只能为1或者2（Check约束）
alter table Student add constraint CK_StudentSex check(StudentSex='1' or StudentSex='2')
-- 分数表 Score.Score 字段只能大于等于0（check约束）
alter table Score add constraint CK_Score check(Score>='0')
-- 课程表 Course.CourseCredit 字段只能大于0（check约束）
alter table Course add constraint CK_CourseCredit check(CourseCredit>'0')
-- 班级课程表 ClassCourse的 ClassId 对应是 班级表ClassId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassId foreign key(ClassId) references Class(ClassId)
-- 班级课程表 CourseId 对应是 课程表 CourseId 的外键 （外键约束）
alter table ClassCourse add constraint FK_CourseId foreign key(CourseId) references Course(CourseId)
-- 分数表 Score 的 StudentId 对应是 学生表StudentId 的外键 （外键约束）
alter table Score add constraint FK_StudentId foreign key(StudentId) references Student(StudentId)
-- 分数表 CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table Score add constraint FK_CourseId foreign key(CourseId) references Course(CourseId)
go
--------------------
-- 插入数据部分
--------------------
-- 学校开设了3个班级：软件一班、软件二班、计算机应用技术班。请插入班级表相关数据
insert into Class(ClassName)
values('软件一班'),('软件二班'),('计算机应用技术班')
select * from Class
go
-- 软件一班有3个同学，姓名、性别、生日、家庭住址  分别是：
--	刘正、男、2000-01-01、广西省桂林市七星区空明西路10号鸾东小区
--	黄贵、男、2001-03-20、江西省南昌市青山湖区艾溪湖南路南150米广阳小区
--	陈美、女、2000-07-08、福建省龙岩市新罗区曹溪街道万达小区
insert into Student(StudentName,StudentSex,StudentBirthday,StudentAddress,ClassId)
values('刘正','1','2000-01-01','广西省桂林市七星区空明西路10号鸾东小区',1),
('黄贵','1','2001-03-20','江西省南昌市青山湖区艾溪湖南路南150米广阳小区',1),
('陈美','2','2000-07-08','福建省龙岩市新罗区曹溪街道万达小区',1)
go
select * from Student
-- 软件二班有2个同学，姓名、性别、生日、家庭住址  分别是：
--	江文、男、2000-08-10、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
--	钟琪、女、2001-03-21、湖南省长沙市雨花区红花坡社区
insert into Student(StudentName,StudentSex,StudentBirthday,StudentAddress,ClassId)
values('江文','1','2000-08-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光',2),
('钟琪','2','2001-03-21','湖南省长沙市雨花区红花坡社区',2)
go
select * from Student
-- 计算机应用技术班有4个同学，姓名、性别、生日、家庭住址  分别是：
--	曾小林、男、1999-12-10、安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光
--	欧阳天天、女、2000-04-05、湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府
--	徐长卿、男、2001-01-30、江苏省苏州市苏州工业园区独墅湖社区
--	李逍遥、男、1999-11-11、广东省广州市白云区金沙洲岛御洲三街恒大绿洲
insert into Student(StudentName,StudentSex,StudentBirthday,StudentAddress,ClassId)
values('曾小林','1','1999-12-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光',3),
('欧阳天天','2','2000-04-05','湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府',3),
('徐长卿','1','2001-01-30','江苏省苏州市苏州工业园区独墅湖社区',3),
('李逍遥','1','1999-11-11','广东省广州市白云区金沙洲岛御洲三街恒大绿洲',3)
go
select * from Student
-- 软件一班开设课程，课程名称和学分分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计.net基础、6
insert into Course(CourseName,CourseScore)
values('数据库高级应用','3'),
('javascript编程基础','3'),
('web前端程序设计基础','4'),
('动态网页设计.net基础','6')
go
select * from Course
-- 软件二班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计.net基础、6



-- 计算机应用技术班开设课程，课程名称和学时分别为：
--	数据库高级应用、3
--	javascript编程基础、3
--	web前端程序设计基础、4
--	动态网页设计php基础、6



-- 考试完成后，各学生各课程得分：
--	刘正、数据库高级应用、80
--	刘正、javascript编程基础、78
--	刘正、web前端程序设计基础、65
--	刘正、动态网页设计.net基础、90
--	黄贵、数据库高级应用、60
--	黄贵、javascript编程基础、77
--	黄贵、web前端程序设计基础、68
--	黄贵、动态网页设计.net基础、88
--	陈美、数据库高级应用、88
--	陈美、javascript编程基础、45
--	陈美、web前端程序设计基础、66
--	陈美、动态网页设计.net基础、75


--	江文、数据库高级应用、56
--	江文、javascript编程基础、80
--	江文、web前端程序设计基础、75
--	江文、动态网页设计.net基础、66
--	钟琪、数据库高级应用、88
--	钟琪、javascript编程基础、79
--	钟琪、web前端程序设计基础、72
--	钟琪、动态网页设计.net基础、85




--	曾小林、数据库高级应用、68
--	曾小林、javascript编程基础、88
--	曾小林、web前端程序设计基础、73
--	曾小林、动态网页设计php基础、63
--	欧阳天天、数据库高级应用、84
--	欧阳天天、javascript编程基础、90
--	欧阳天天、web前端程序设计基础、92
--	欧阳天天、动态网页设计php基础、78
--	徐长卿、数据库高级应用、58
--	徐长卿、javascript编程基础、59
--	徐长卿、web前端程序设计基础、65
--	徐长卿、动态网页设计php基础、75
--	李逍遥、数据库高级应用、48
--	李逍遥、javascript编程基础、67
--	李逍遥、web前端程序设计基础、71
--	李逍遥、动态网页设计.net基础、56

--------------------
-- 删除数据部分
--------------------
-- 分数表这边 分数表 最后一条插入重复了，请帮忙删除这条数据



--------------------
-- 修改数据部分
--------------------
-- 计算机应用技术班 的 欧阳天天 生日写错了，正确的生日应该是：2000-04-06，请用sql进行修改。（update）

-- 计算机应用技术班 的 徐长卿 的 javascript编程基础 分数填错，正确的分数应该是：61，请用sql进行修改。（update）


--------------------
-- 查询数据部分
--------------------
-- 查询所有的班级信息（简单select）


-- 查询所有的学生信息（简单select）


-- 查询所有的课程信息（简单select）


-- 查询所有的学生信息，并且按照年龄从小到大排列（简单select + 排序）


-- 查询软件一班所有的学生（简单where）


-- 查询所有的女生（简单where）


-- 查询2000年出生的所有学生（简单where）


-- 查询姓是欧阳的学生（模糊查询，）


-- 询地址是桂林市的学生（模糊查询）


-- 查询姓李的学生，并且是三个字的（模糊查询 + 下划线）


-- 查询 软件一班 有几个学生（聚合查询）


-- 查询 软件一班 有几门课程（聚合查询）


-- 查询 刘正(学生编号为1) 的总分（聚合查询）


-- 查询所有学生 数据库高级应用 的平均分（聚合查询）


-- 查询 所有学生 的总分（聚合查询 + 分组）


-- 查询 所有学生 的平均分（聚合查询 + 分组）


-- 查询 所有学生 的平均分，并且按照平均分从高到低排列（聚合查询 + 分组 + 排序）


-- 查询 所有学生 的平均分，并且按照平均分从高到低排列（聚合查询 + 分组 + 排序）


-- 查询 所有学生中 平均分 大于80分的学生，（聚合查询 + 分组 + having）


-- 查询 李逍遥(编号id为9) 所在的班级名称（连接查询 2表）


-- 查询 李逍遥(学生编号id为9) 学习的课程有哪几门，需要查询出课程名称，课程学分（连接查询）


-- 查询 李逍遥(学生编号id为9) 学习的课程考试得分，需要姓名、课程名称、得分（连接查询）


-- 查询 软件一班的每个学生 的平均分（聚合查询 + 子查询）


-- 查询 软件二班的每个学生 的平均分（聚合查询 + 连接查询）


-- 按照班级查询所有课程的平均分，并且按照平均分高低进行排序


